import datetime
from other.cfg import Config
from other.change_format import change_format
from other.status import Status
from server.Mysql import MySQL


class sqlMontage:
    def __init__(self):
        self.cfg = Config()
        self.st = Status()
        self.db = MySQL(self.cfg.res['mysql']['database'], 'sys_user')
        self.cf = change_format()
        self.table_data = None
        # 必填字段
        self.required_fields = None
        # 待处理数据
        self.data = None

    def start(self, data, table_name):
        # 获取数据表结构
        self.table_data = self.db.construction(table_name)
        print(self.table_data)
        # 必填字段列表
        self.required_fields = list(self.table_data.keys())
        print('self.required_fields', self.required_fields)
        # 将数据转成_模式
        data = self.cf.changes_format(data, to_snake=True)
        # 调用数据过滤和转换
        self.data = self.filter_and_convert_data(data, self.required_fields, self.table_data)
        print("Converted Data:")
        print(self.data)

    # 过滤并转换数据
    def filter_and_convert_data(self, json_data, required_fields, field_types):
        converted_data = {}
        for field in required_fields:
            if field in json_data:
                field_type = field_types.get(field)
                value = json_data[field]
                if value is not None:
                    # 转换字段值为对应的类型
                    try:
                        converted_data[field] = self.convert_value(value, field_type)
                    except ValueError as e:
                        print(f"Error converting field '{field}': {e}")

        return converted_data

    # 转换字段值为指定的类型
    def convert_value(self, value, field_type):
        """根据字段类型转换值"""
        if field_type == "int":
            try:
                return int(value)
            except ValueError:
                raise ValueError(f"Invalid value for int: {value}")
        elif field_type == "float":
            try:
                return float(value)
            except ValueError:
                raise ValueError(f"Invalid value for float: {value}")
        elif field_type == "str":
            return str(value)
        elif field_type == "datetime":
            try:
                # 假设日期格式为 'YYYY-MM-DD'，根据实际情况修改
                return datetime.datetime.now().strftime('%Y-%m-%d %H:%M:%S')
                # return value
            except ValueError:
                raise ValueError(f"Invalid date format for datetime: {value}")
        else:
            raise ValueError(f"Unsupported field type: {field_type}")

    # 添加数据
    def add_sql_data(self, data, table_name):
        self.start(data, table_name)
        columns = ", ".join([f"`{key}`" for key in self.data.keys()])  # 列名加反引号
        values = ", ".join([f"'{v}'" if isinstance(v, str) else f"{v}" for v in self.data.values()])
        sql = f"INSERT INTO `{table_name}` ({columns}) VALUES ({values})"
        sql = sql.replace("'None'", "NULL").replace("None", "NULL")
        return sql

    # 生成更新 SQL 语句
    def edit_sql_data(self, data, table_name, key, values):
        self.start(data, table_name)
        set_clause = ", ".join(
            [f"{key} = '{value}'" if isinstance(value, str) else f"`{key}` = {value}" for key, value in
             self.data.items()])
        # 判断 values 类型，如果是字符串则加引号
        if isinstance(values, str):
            values = f"'{values}'"
        sql = f"UPDATE `{table_name}` SET {set_clause} WHERE `{key}` = {values}"
        sql = sql.replace("'None'", "NULL").replace("None", "NULL")
        return sql
